package com.campus.server.dao;

import com.campus.common.util.DBUtil;
import com.campus.common.vo.AcademicRecord;
import com.campus.common.vo.Teacher;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TeacherAccountManagementDao {
    // 获取所有书籍
    public List<Teacher> listAllTeachers() {
        List<Teacher> teachers = new ArrayList<>();
        String sql = "SELECT * FROM teacher1";

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                teachers.add(new Teacher(
                        rs.getString("teacherId"),
                        rs.getString("name"),
                        rs.getString("sex"),
                        rs.getInt("age"),
                        rs.getString("department"),
                        rs.getString("contactInfo")
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return teachers;
    }


    // 新增学生
    public boolean addTeacher(Teacher teacher) {
        String sql = "INSERT INTO teacher1 (teacherId, name, sex, age, department, contactInfo) VALUES (?, ?, ?, ?, ?, ?) ";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, teacher.getTeacherId());
            stmt.setString(2, teacher.getName());
            stmt.setString(3, teacher.getSex());
            stmt.setInt(4, teacher.getAge());
            stmt.setString(5, teacher.getDepartment());
            stmt.setString(6, teacher.getContactInfo());

            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 删除教师
    public boolean deleteTeacher(String teacherId) {
        String sql_1 = "DELETE FROM user1 WHERE username = ?";
        String sql_2 = "DELETE FROM teacher1 WHERE teacherId = ?";
        int rowsAffected_1 = 0;
        int rowsAffected_2 = 0;

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt_1 = conn.prepareStatement(sql_1);
             PreparedStatement stmt_2 = conn.prepareStatement(sql_2)) {

            stmt_1.setString(1, teacherId);
            stmt_2.setString(1, teacherId);
            rowsAffected_1 = stmt_1.executeUpdate();
            rowsAffected_2 = stmt_2.executeUpdate();
            System.out.println("teacherId=" + teacherId);
            System.out.println("rowsAffected = " + rowsAffected_1);
            System.out.println(rowsAffected_1>0);
            return rowsAffected_1*rowsAffected_2 > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 根据ID查询书籍
    public Teacher getTeacherById(String teacherId) {
        String sql = "SELECT * FROM teacher1 WHERE teacherId = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, teacherId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return new Teacher(
                        rs.getString("teacherId"),
                        rs.getString("name"),
                        rs.getString("sex"),
                        rs.getInt("age"),
                        rs.getString("department"),
                        rs.getString("contactInfo")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    // 根据书名查询书籍（模糊查询）
    public List<Teacher> searchTeachersById(String teacherId) {
        List<Teacher> teachers = new ArrayList<>();
        String sql = "SELECT * FROM teacher1 WHERE teacherId LIKE ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, "%" + teacherId + "%");
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                teachers.add(new Teacher(
                                rs.getString("teacherId"),
                                rs.getString("name"),
                                rs.getString("sex"),
                                rs.getInt("age"),
                                rs.getString("department"),
                                rs.getString("contactInfo")
                        )
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return teachers;
    }

    // 根据作者查询书籍（模糊查询）
    public List<Teacher> searchTeachersByName(String name) {
        List<Teacher> teachers = new ArrayList<>();
        String sql = "SELECT * FROM teacher1 WHERE name LIKE ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, "%" + name + "%");
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                teachers.add(new Teacher(
                                rs.getString("teacherId"),
                                rs.getString("name"),
                                rs.getString("sex"),
                                rs.getInt("age"),
                                rs.getString("department"),
                                rs.getString("contactInfo")
                        )
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return teachers;
    }

    // 综合查询（根据书名和作者模糊查询）
    public List<Teacher> searchTeachers(String studentId, String name) {
        List<Teacher> teachers = new ArrayList<>();

        // 构建动态SQL
        StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM teacher1 WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (studentId != null && !studentId.trim().isEmpty()) {
            sqlBuilder.append(" AND teacherId LIKE ?");
            params.add("%" + studentId + "%");
        }

        if (name != null && !name.trim().isEmpty()) {
            sqlBuilder.append(" AND name LIKE ?");
            params.add("%" + name + "%");
        }

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sqlBuilder.toString())) {

            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                teachers.add(new Teacher(
                                rs.getString("teacherId"),
                                rs.getString("name"),
                                rs.getString("sex"),
                                rs.getInt("age"),
                                rs.getString("department"),
                                rs.getString("contactInfo")
                        )
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return teachers;
    }

    // 更新书籍信息
    public boolean updateTeacher(Teacher teacher) {
        String sql = "UPDATE teacher1 SET name = ?, sex = ?, age = ?, department = ?, contactInfo = ? WHERE teacherId = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(6, teacher.getTeacherId());
            stmt.setString(1, teacher.getName());
            stmt.setString(2, teacher.getSex());
            stmt.setInt(3, teacher.getAge());
            stmt.setString(4, teacher.getDepartment());
            stmt.setString(5, teacher.getContactInfo());

            int rowsAffected = stmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 检查书籍是否存在
    public boolean teacherExists(String teacherId) {
        String sql = "SELECT COUNT(*) FROM teacher1 WHERE teacherId = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, teacherId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}
